﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MEMDB
{
    public partial class StudentManagementSystem : Form
    {
        string connStr = "connect to server='";
        const string insertStr = "insert into Student_Info(STUDY_NO, BIRTHDAY, NAME, GPA, RANK) values(@Study_No, @Birthday, @Name, @Gpa, @Rank)";
        const string selectByName = "select * from Student_Info where NAME = @Name";
        const string selectByStudyNo = "select * from Student_Info where STUDY_NO = @StudyNo";
        const string selectByNameAndNo = "select * from Student_Info where NAME = @Name and STUDY_NO = @StudyNo";
        const string selectByGpa = "select * from Student_Info where GPA = @Gpa";
        const string selectByRank = "select * from Student_Info where RANK = @Rank";
        const string selectByGpaAndRank = "select * from Student_Info where  GPA = @Gpa and RANK = @Rank";

        string refreshString = "";

        MemDBAPI.SQLConnection conn = new MemDBAPI.SQLConnection();

        private bool bFlag = false;
        private string m_privatekey = "";

        public StudentManagementSystem(string server)
        {
            InitializeComponent();
            connStr += server;
            connStr += "' user='b098' password='b098' database='DemoDB';";
        }

        private void StudentManagementSystem_Load(object sender, EventArgs e)
        {
            conn.ConnectString = connStr;
            this.comboBox1.SelectedIndex = 0;
            this.comboBox2.SelectedIndex = 0;
            try
            {
                if (conn.Open() != true)
                {
                    lbMsg.Text = "连接失败。";
                    this.Dispose();
                }
                lbMsg.Text = "连接成功。";
                this.toolStripStatusLabel1.Text = "已连接至数据库";
            }
            catch (MemDBAPI.SQLException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // Insert
        private void button1_Click(object sender, EventArgs e)
        {
            string studyNo = textBox2.Text.Trim();
            string name = textBox1.Text.Trim();
            string birthday = dateTimePicker1.Value.ToString();
            float gpa = 0;
            int rank = 0;
            if (studyNo.Length > 10 || name.Length > 20)
            {
                MessageBox.Show("您输入的格式有误。请注意数据的长度限制。");
                return;
            }
            try
            {
                gpa = (float)Convert.ToDouble(textBox3.Text);
                rank = Convert.ToInt32(this.numericUpDown1.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
            MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(insertStr, conn);
            cmd.Parameters.Add("@Study_No", MemDBAPI.SqlType.NChar).m_Value = studyNo;
            cmd.Parameters.Add("@Birthday", MemDBAPI.SqlType.NChar).m_Value = birthday;
            cmd.Parameters.Add("@Name", MemDBAPI.SqlType.NChar).m_Value = name;
            cmd.Parameters.Add("@Gpa", MemDBAPI.SqlType.Float).m_Value = gpa;
            cmd.Parameters.Add("@Rank", MemDBAPI.SqlType.Int).m_Value = rank;
            try
            {
                cmd.ExecuteNonQuery();
                lbMsg.Text = "插入一条数据成功。";
            }
            catch (MemDBAPI.SQLException ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        // 依靠学号姓名检索
        private void button2_Click(object sender, EventArgs e)
        {
            bFlag = true;
            string studyNo = textBox4.Text.Trim();
            string name = textBox5.Text.Trim();
            if (studyNo.Length > 10 || name.Length > 20)
            {
                MessageBox.Show("您输入的格式有误。请注意数据的长度限制。");
                return;
            }
            if (studyNo.Length == 0 && name.Length == 0)
            {
                MessageBox.Show("您应该至少输入一个查询条件。");
                return;
            }
            else if (studyNo.Length != 0 && name.Length == 0)
            {
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByStudyNo, conn);
                cmd.Parameters.Add("@StudyNo", MemDBAPI.SqlType.NChar).m_Value = studyNo;
                try
                {
                    MemDBAPI.ResultDataSet ds = cmd.ExecuteQuery();
                    if (ds == null)
                    {
                        lbMsg.Text = "为查询到任何信息。";
                        return;
                    }
                    refreshString = cmd.m_commandString;

                    ds.FillGridView(this.dataGridView1);
                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            else if (studyNo.Length == 0 && name.Length != 0)
            {
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByName, conn);
                cmd.Parameters.Add("@Name", MemDBAPI.SqlType.NChar).m_Value = name;

                try
                {
                    MemDBAPI.ResultDataSet ds = cmd.ExecuteQuery();
                    ds.FillGridView(this.dataGridView1);
                    refreshString = cmd.m_commandString;

                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            else
            {
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByNameAndNo, conn);
                cmd.Parameters.Add("@StudyNo", MemDBAPI.SqlType.NChar).m_Value = studyNo;
                cmd.Parameters.Add("@Name", MemDBAPI.SqlType.NChar).m_Value = name;
                try
                {
                    MemDBAPI.ResultDataSet ds = cmd.ExecuteQuery();
                    ds.FillGridView(this.dataGridView1);
                    refreshString = cmd.m_commandString;

                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }

            bFlag = false;
        }

        // 依靠成绩和排名检索
        private void button3_Click(object sender, EventArgs e)
        {
            bFlag = true;
            float gpa = 0;
            int rank = 0;
            string strGpa = textBox7.Text.Trim();
            string strRank = textBox6.Text.Trim();
            // 两个条件
            if (strGpa.Length > 0 && strRank.Length > 0)
            {
                if(this.comboBox1.SelectedIndex == -1 || this.comboBox2.SelectedIndex == -1)
                {
                    MessageBox.Show("请选择运算符。");
                    return;
                }
                try
                {
                    gpa = (float)Convert.ToDouble(strGpa);
                    rank = Convert.ToInt32(strRank);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByGpaAndRank, conn);
                string[] subTemp = cmd.m_commandString.Split('=');
                
                cmd.m_commandString = "";
                cmd.m_commandString += subTemp[0] + " ";
                cmd.m_commandString += this.comboBox1.Text + " ";
                cmd.m_commandString += subTemp[1] + " ";
                cmd.m_commandString += this.comboBox2.Text + " ";
                cmd.m_commandString += subTemp[2];

                cmd.Parameters.Add("@Gpa", MemDBAPI.SqlType.Float).m_Value = gpa;
                cmd.Parameters.Add("@Rank", MemDBAPI.SqlType.Int).m_Value = rank;
                try
                {
                    MemDBAPI.ResultDataSet ds =  cmd.ExecuteQuery();
                    ds.FillGridView(this.dataGridView1);
                    refreshString = cmd.m_commandString;

                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }

            // GPA 条件
            else if (strGpa.Length > 0)
            {
                if (this.comboBox1.SelectedIndex == -1)
                {
                    MessageBox.Show("请选择运算符。");
                    return;
                }
                try
                {
                    gpa = (float)Convert.ToDouble(strGpa);  
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByGpa, conn);
                cmd.m_commandString = cmd.m_commandString.Replace("=", this.comboBox1.Text);
                cmd.Parameters.Add("@Gpa", MemDBAPI.SqlType.Float).m_Value = gpa;
                try
                {
                    MemDBAPI.ResultDataSet ds =  cmd.ExecuteQuery();
                    refreshString = cmd.m_commandString;
                    
                    ds.FillGridView(this.dataGridView1);

                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }

            //  RANK 条件
            else if (strRank.Length > 0)
            {
                if (this.comboBox2.SelectedIndex == -1)
                {
                    MessageBox.Show("请选择运算符。");
                    return;
                }
                try
                {
                    rank = Convert.ToInt32(strRank);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
                MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(selectByRank, conn);
                cmd.m_commandString = cmd.m_commandString.Replace("=", this.comboBox2.Text);
                cmd.Parameters.Add("@Rank", MemDBAPI.SqlType.Int).m_Value = rank;
                try
                {
                    MemDBAPI.ResultDataSet ds = cmd.ExecuteQuery();

                    refreshString = cmd.m_commandString;
                    ds.FillGridView(this.dataGridView1);
                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            else
            {
                MessageBox.Show("请至少选择一项条件。");
            }

            bFlag = false;
        }


        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string strCmd = "delete from Student_Info where STUDY_NO = @StudyNo";
            MemDBAPI.SQLCommand cmd = new MemDBAPI.SQLCommand(strCmd, conn);
            string strTemp = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            cmd.Parameters.Add("@StudyNo", MemDBAPI.SqlType.NChar).m_Value = strTemp.Trim(new char[] {'\0', ' '});
            try
            {
                cmd.ExecuteNonQuery();
                lbMsg.Text = cmd.getRetMsg();
                bFlag = true;
                cmd = new MemDBAPI.SQLCommand(refreshString, conn);
                MemDBAPI.ResultDataSet ds = cmd.ExecuteQuery();
                ds.FillGridView(this.dataGridView1);
                bFlag = false;
            }
            catch (MemDBAPI.SQLException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            string strCmd;
            MemDBAPI.SQLCommand cmd = null;
            if (! bFlag)
            {
                switch(e.ColumnIndex)
                {
                    case 0:
                        strCmd = "update Student_Info set STUDY_NO = @StudyNewNo where STUDY_NO = @StudyOldNo";
                        cmd = new MemDBAPI.SQLCommand(strCmd, conn);
                        cmd.Parameters.Add("@StudyNewNo", MemDBAPI.SqlType.NChar).m_Value = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString().Trim(new char[] { '\0', ' ' });
                        break;
                    case 1:
                        strCmd = "update Student_Info set NAME = @Name where STUDY_NO = @StudyOldNo";
                        cmd = new MemDBAPI.SQLCommand(strCmd, conn);
                        cmd.Parameters.Add("@Name", MemDBAPI.SqlType.NChar).m_Value = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString().Trim(new char[] { '\0', ' ' });
                        break;
                    case 2:
                        strCmd = "update Student_Info set BIRTHDAY = @Birthday where STUDY_NO = @StudyOldNo";
                        cmd = new MemDBAPI.SQLCommand(strCmd, conn);
                        cmd.Parameters.Add("@Birthday", MemDBAPI.SqlType.NChar).m_Value = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString().Trim(new char[] { '\0', ' ' });
                        break;
                    case 3:
                        strCmd = "update Student_Info set GPA = @Gpa where STUDY_NO = @StudyOldNo";
                        cmd = new MemDBAPI.SQLCommand(strCmd, conn);
                        cmd.Parameters.Add("@Gpa", MemDBAPI.SqlType.Float).m_Value = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString().Trim(new char[] { '\0', ' ' });
                        break;
                    case 4:
                        strCmd = "update Student_Info set RANK = @Rank where STUDY_NO = @StudyOldNo";
                        cmd = new MemDBAPI.SQLCommand(strCmd, conn);
                        cmd.Parameters.Add("@Rank", MemDBAPI.SqlType.Int).m_Value = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString().Trim(new char[] { '\0', ' ' });
                        break;
                }

                cmd.Parameters.Add("@StudyOldNo", MemDBAPI.SqlType.NChar).m_Value = m_privatekey;

                try
                {
                    cmd.ExecuteNonQuery();
                    lbMsg.Text = cmd.getRetMsg();
                }
                catch (MemDBAPI.SQLException ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void StudentManagementSystem_FormClosed(object sender, FormClosedEventArgs e)
        {
            conn.Close();
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (!bFlag && this.dataGridView1.SelectedRows.Count > 0 && this.dataGridView1.SelectedRows[0].Cells.Count > 0 && this.dataGridView1.SelectedRows[0].Cells[0].Value != null)
            {
                m_privatekey = this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString().Trim(new char[] { '\0', ' ' });
            }
        }


        private void groupBox1_Enter(object sender, EventArgs e)
        {
            this.lbMsg.Text = "已连接。";
        }

        private void groupBox3_Enter(object sender, EventArgs e)
        {
            this.lbMsg.Text = "已连接。";
        }

        private void groupBox4_Enter(object sender, EventArgs e)
        {
             this.lbMsg.Text = "已连接。";
        }
    }
}
